<?php

class DBMCommandTranslator implements DBMTranslator {
	/**
	 * Translate the select command to SQL Server notation
	 *
	 * @param DBMCommand $command
	 */
	public static function translateToSQLServer($command, $cmd = null) {
		if($command->getOperation() == DBMOperations::SELECT){
			return self::createSQLServerSelect($command);
		} elseif($command->getOperation() == DBMOperations::INSERT) {
			return self::createSQLServerInsert($command);
		} elseif($command->getOperation() == DBMOperations::UPDATE) {
			return self::createSQLServerUpdate($command);
		} elseif($command->getOperation() == DBMOperations::DELETE) {
			return self::createSQLServerDelete($command);
		} else {
			throw new UnsupportedOperationException($command->getOperation());
		}
	}
	
	/**
	 * Translate the select command to MySQL notation
	 *
	 * @param DBMCommand $command
	 */
	public static function translateToMysql($command, $cmd = null) {
		if($command->getOperation() == DBMOperations::SELECT){
			return self::createMySQLSelect($command);
		} elseif($command->getOperation() == DBMOperations::INSERT) {
			return self::createMySQLInsert($command);
		} elseif($command->getOperation() == DBMOperations::UPDATE) {
			return self::createMySQLUpdate($command);
		} elseif($command->getOperation() == DBMOperations::DELETE) {
			return self::createMySQLDelete($command);
		} else {
			throw new UnsupportedOperationException($command->getOperation());
		}
	}
	
	/**
	 * Translate the select command to PostgreSQL notation
	 *
	 * @param DBMCommand $command
	 */
	public static function translateToPostgreSQL($command, $cmd = null) {
		throw new UnsupportedDatabaseException(DBManager::TYPE_POSTGRESQL);
	}

	/**
	 * Create a new SELECT query for SQL Server 
	 *
	 * @param DBMCommand $command
	 * @return DBMQueryRequest The query object
	 */
	private static function createSQLServerSelect(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entities	= $command->getEntities();
		$joins		= $command->getJoins();
		$filters 	= $command->getFilters();
		$ordenators	= $command->getOrdenators();
		
		// set the buffer variables
		$joinsBuffer 		= array();
		$fieldsBuffer 		= array();
		$filtersBuffer 		= array();
		$ordenatorsBuffer	= array();
		
		// search the tables
		foreach($entities as $entity){
			// get the fields
			$attributes = $entity->getAttributes();

			// search the fields
			foreach($attributes as $attribute){
				// check if the attribute has a column
				if(is_null($attribute->getColumn())){
					continue;
				}
				
				// translate
				$translated = DBMAttributeTranslator::translateToSQLServer($attribute, $command);
				
				// check the integrity of the translated data
				if(is_null($translated)){
					continue;
				}
						
				// add the field in the buffer
				$fieldsBuffer[] = $translated;
					
				// store the field on the query
				$query->addField($attribute->__toString());
			}
		}
		
		// search and translate the joins
		foreach($joins as $join){
			// translate the join
			$joinsBuffer[] = DBMJoinTranslator::translateToSQLServer($join, $command);
			
			// check if the entity of the join has direct relationship with the main entity
			if($command->getMainEntity()->haveRelationshipWith($join->getEntity())){
				// get the fields
				$attributes = $join->getEntity()->getAttributes();
	
				// search the fields
				foreach($attributes as $attribute){
					// check if the attribute has a column
					if(is_null($attribute->getColumn())){
						continue;
					}
					
					// translate
					$translated = DBMAttributeTranslator::translateToSQLServer($attribute, $command);
					
					// check the integrity of the translated data
					if(is_null($translated)){
						continue;
					}
							
					// add the field in the buffer
					$fieldsBuffer[] = $translated;
						
					// store the field on the query
					$query->addField($attribute->__toString());
				}
			}
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MSSQL, $command);
		}
		
		// search and translate the ordenators
		foreach($ordenators as $ordenator){
			$ordenatorsBuffer[] = DBMOrdenatorTranslator::translateToSQLServer($ordenator);
		}
		
		// create a textual query
		$sql = sprintf("SELECT %s %s %s FROM %s %s %s %s",
			($command instanceof DBMSelectCommand && $command->isDistinct() == true) ? "DISTINCT" : "", // distinct	
			($command->getResultLimit() == null) ? "" : "TOP " . $command->getResultLimit(), // result limit
			implode(", ", $fieldsBuffer),  // fields
			DBMEntityTranslator::translateToSQLServer($command->getMainEntity()), // main table
			implode(" ", $joinsBuffer),  // joins
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "", // filters
			(count($ordenatorsBuffer) > 0) ? "ORDER BY " . implode(", ", $ordenatorsBuffer) : ""); // ordenators
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}

	/**
	 * Create a new INSERT query for SQL Server
	 *
	 * @param DBMCommand $command
	 * @return DBMQueryRequest
	 */
	private static function createSQLServerInsert(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// set the buffer variables
		$attributesBuffer = array();
		$valuesBuffer = array();
		
		// get the fields
		$attributes = $entity->getAttributes();

		// search the fields
		foreach($attributes as $attribute){
			// check if the attribute has a column
			if(is_null($attribute->getColumn()) || $attribute->isInsertable() === false){
				continue;
			}
			
			// translate
			$translatedAttribute 	= DBMAttributeTranslator::translateToSQLServer($attribute, $command);
			$translatedValue		= DBMGeneralTranslator::translateToSQLServer($attribute->getValue(), $command);
			
			// check the integrity of the translated data
			if(is_null($translatedAttribute) || is_null($translatedValue) || $translatedValue === "NULL"){
				continue;
			}
			
			// add the field in the buffer
			$attributesBuffer[] = $translatedAttribute;
			$valuesBuffer[] = $translatedValue;
			
			// store the field on the query
			$query->addField($attribute->getColumn());
		}
		
		// return an textual query
		$sql = sprintf("INSERT INTO %s(%s) VALUES (%s)",
			DBMEntityTranslator::translateToSQLServer($entity, $command), 
			implode(", ", $attributesBuffer),  // attributes
			implode(", ", $valuesBuffer));
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	private static function createSQLServerUpdate(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// get the fields
		$attributes = $entity->getAttributes();
		
		// get the filters
		$filters = $command->getFilters();	
		
		// get the joins
		$joins = $command->getJoins();
		
		// set the buffer variables
		$attributesBuffer 	= array();
		$filtersBuffer 		= array();
		$joinsBuffer 		= array();
		
		// translate the main entity
		$translatedEntity = DBMEntityTranslator::translateToSQLServer($entity, $command);

		// search the fields
		foreach($attributes as $attribute){
			// check if the attribute has a column
			if(is_null($attribute->getColumn()) ||  is_null($attribute->getValue()) || $attribute->isUpdateable() === false){
				continue;
			}
			
			// translate
			$attributesBuffer[] = DBMAttributeTranslator::translateToSQLServer($attribute, $command);
			
			// store the field on the query
			$query->addField($attribute->getColumn());
		}
		
		// search and translate the joins
		foreach($joins as $join){
			$joinsBuffer[] = DBMJoinTranslator::translateToSQLServer($join, $command);
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MSSQL, $command);
		}
		
		// return an textual query
		$sql = sprintf("UPDATE %s SET %s FROM %s %s %s",
			$translatedEntity, 
			implode(", ", $attributesBuffer),  // attributes
			$translatedEntity, 
			implode(" ", $joinsBuffer), 
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "");
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	private static function createSQLServerDelete(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// get the filters
		$filters = $command->getFilters();	
		
		// get the joins
		$joins = $command->getJoins();
		
		// set the buffer variables
		$filtersBuffer 			= array();
		$joinsBuffer 			= array();
		
		// translate the main entity
		$translatedEntity = DBMEntityTranslator::translateToSQLServer($entity, $command);
		
		// search and translate the joins
		foreach($joins as $join){
			$joinsBuffer[] = DBMJoinTranslator::translateToSQLServer($join, $command);
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MSSQL, $command);
		}
		
		// return an textual query
		$sql = sprintf("DELETE FROM %s %s %s",
			$translatedEntity, 
			implode(" ", $joinsBuffer), 
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "");
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	/**
	 * Create a new SELECT query for MySQL 
	 *
	 * @param DBMCommand $command
	 * @return DBMQueryRequest The query object
	 */
	private static function createMySQLSelect(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entities	= $command->getEntities();
		$joins		= $command->getJoins();
		$filters 	= $command->getFilters();
		$ordenators	= $command->getOrdenators();
		
		// set the buffer variables
		$joinsBuffer 		= array();
		$fieldsBuffer 		= array();
		$filtersBuffer 		= array();
		$ordenatorsBuffer	= array();
		
		// search the tables
		foreach($entities as $entity){
			// get the fields
			$attributes = $entity->getAttributes();

			// search the fields
			foreach($attributes as $attribute){
				// check if the attribute has a column
				if(is_null($attribute->getColumn())){
					continue;
				}
				
				// translate
				$translated = DBMAttributeTranslator::translateToMySQL($attribute, $command);
				
				// check the integrity of the translated data
				if(is_null($translated)){
					continue;
				}
						
				// add the field in the buffer
				$fieldsBuffer[] = $translated;
					
				// store the field on the query
				$query->addField($attribute->__toString());
			}
		}
		
		// search and translate the joins
		foreach($joins as $join){
			// translate the join
			$joinsBuffer[] = DBMJoinTranslator::translateToMySQL($join, $command);
			
			// check if the entity of the join has direct relationship with the main entity
			if($command->getMainEntity()->haveRelationshipWith($join->getEntity())){
				// get the fields
				$attributes = $join->getEntity()->getAttributes();
	
				// search the fields
				foreach($attributes as $attribute){
					// check if the attribute has a column
					if(is_null($attribute->getColumn())){
						continue;
					}
					
					// translate
					$translated = DBMAttributeTranslator::translateToMySQL($attribute, $command);
					
					// check the integrity of the translated data
					if(is_null($translated)){
						continue;
					}
							
					// add the field in the buffer
					$fieldsBuffer[] = $translated;
						
					// store the field on the query
					$query->addField($attribute->__toString());
				}
			}
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MYSQL, $command);
		}
		
		// search and translate the ordenators
		foreach($ordenators as $ordenator){
			$ordenatorsBuffer[] = DBMOrdenatorTranslator::translateToMySQL($ordenator);
		}
		
		// create a textual query
		$sql = sprintf("SELECT %s %s FROM %s %s %s %s %s",
			($command instanceof DBMSelectCommand && $command->isDistinct() == true) ? "DISTINCT" : "", // distinct	
			implode(", ", $fieldsBuffer),  // fields
			DBMEntityTranslator::translateToMySQL($command->getMainEntity()), // main table
			implode(" ", $joinsBuffer),  // joins
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "", // filters
			(count($ordenatorsBuffer) > 0) ? "ORDER BY " . implode(", ", $ordenatorsBuffer) : "", // ordenators
			(is_null($command->getResultLimit())) ? "" : "LIMIT " . $command->getResultLimit()); // result limit
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	/**
	 * Create a new INSERT query for MySQL
	 *
	 * @param DBMCommand $command
	 * @return DBMQueryRequest
	 */
	private static function createMySQLInsert(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// set the buffer variables
		$attributesBuffer = array();
		$valuesBuffer = array();
		
		// get the fields
		$attributes = $entity->getAttributes();

		// search the fields
		foreach($attributes as $attribute){
			// check if the attribute has a column
			if(is_null($attribute->getColumn()) || $attribute->isInsertable() == false){
				continue;
			}
			
			// translate
			$translatedAttribute 	= DBMAttributeTranslator::translateToMySQL($attribute, $command);
			$translatedValue		= DBMGeneralTranslator::translateToMySQL($attribute->getValue(), $command);
			
			// check the integrity of the translated data
			if(is_null($translatedAttribute) || is_null($translatedValue) || $translatedValue == "NULL"){
				continue;
			}
				
			// add the field in the buffer
			$attributesBuffer[] = $translatedAttribute;
			$valuesBuffer[] = $translatedValue;
			
			// store the field on the query
			$query->addField($attribute->getColumn());
		}
		
		// return an textual query
		$sql = sprintf("INSERT INTO %s(%s) VALUES (%s)",
			DBMEntityTranslator::translateToMySQL($entity, $command), 
			implode(", ", $attributesBuffer),  // attributes
			implode(", ", $valuesBuffer));
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	/**
	 * Create a new UPDATE query for MySQL
	 *
	 * @param DBMCommand $command
	 * @return DBMQueryRequest
	 */
	private static function createMySQLUpdate(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// get the fields
		$attributes = $entity->getAttributes();
		
		// get the filters
		$filters = $command->getFilters();	
		
		// get the joins
		$joins = $command->getJoins();
		
		// set the buffer variables
		$attributesBuffer 	= array();
		$filtersBuffer 		= array();
		$joinsBuffer 		= array();
		
		// translate the main entity
		$translatedEntity = DBMEntityTranslator::translateToMySQL($entity, $command);

		// search the fields
		foreach($attributes as $attribute){
			// check if the attribute has a column
			if($attribute->getColumn() === null ||  $attribute->getValue() === null || $attribute->isUpdateable() === false){
				continue;
			}
			
			// translate
			$attributesBuffer[] = DBMAttributeTranslator::translateToMySQL($attribute, $command);
			
			// store the field on the query
			$query->addField($attribute->getColumn());
		}
		
		// search and translate the joins
		foreach($joins as $join){
			$joinsBuffer[] = DBMJoinTranslator::translateToMySQL($join, $command);
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MYSQL, $command);
		}
		
		// return an textual query
		$sql = sprintf("UPDATE %s %s SET %s %s",
			$translatedEntity,
			implode(" ", $joinsBuffer), 
			implode(", ", $attributesBuffer),  // attributes
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "");
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
	
	
	/**
	 * Create a new DELETE query for MySQL
	 * @param DBMCommand $command
	 * @return DBMQueryRequest
	 */
	private static function createMySQLDelete(DBMCommand $command){
		// create the query
		$query = new DBMQueryRequest();
		
		// get the tables and the filters on the command
		$entity = $command->getMainEntity();
		
		// get the filters
		$filters = $command->getFilters();	
		
		// get the joins
		$joins = $command->getJoins();
		
		// set the buffer variables
		$filtersBuffer 			= array();
		$joinsBuffer 			= array();
		
		// translate the main entity
		$translatedEntity = DBMEntityTranslator::translateToMySQL($entity, $command);
		
		// search and translate the joins
		foreach($joins as $join){
			$joinsBuffer[] = DBMJoinTranslator::translateToMySQL($join, $command);
		}
		
		// search the filters
		foreach($filters as $filter){
			// add the filter in the buffer
			$filtersBuffer[] = $filter->toSQL(DBManager::TYPE_MYSQL, $command);
		}
		
		// return an textual query
		$sql = sprintf("DELETE FROM %s %s %s",
			$translatedEntity, 
			implode(" ", $joinsBuffer), 
			(count($filtersBuffer) > 0) ? "WHERE " . implode(" AND ", $filtersBuffer) : "");
		
		// set the query
		$query->setQueryString($sql);
		
		// return the query
		return $query;
	}
}

?>